Is there a way to kill a connection from the pg_stat_activitly list?
Is there a way to kill a connection from the pg_stat_activitly list?
am 15.10.2007 14:56:06 von Jessica Richard
--0-1558268327-1192452966=:65196
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
When you see a hanging Postgres connection (or a job running so long and =
you don't want to continue any more) from=20
select * from pg_stat_activity
and you want to disconnect it,
how do you do it?
thanks,
Jessica
=20
---------------------------------
Check out the hottest 2008 models today at Yahoo! Autos.
--0-1558268327-1192452966=:65196
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
When you see a hanging Postgres connection (or a job running so long and =
you don't want to continue any more) from
select * from pg_stat_a=
ctivity
and you want to disconnect it,
how do you do it?
>
thanks,
Jessica
/autos.yahoo.com/new_cars.html;_ylc=3DX3oDMTE5NWVzZGVyBF9TAz k3MTA3MDc2BHN=
lYwNtYWlsdGFncwRzbGsDYXV0b3MtbmV3Y2Fy
">Check out the hottest 2008 models today at Yahoo! Autos.
--0-1558268327-1192452966=:65196--
Re: Is there a way to kill a connection from the pg_stat_activitlylist?
am 15.10.2007 15:23:21 von Tommy Gildseth
Jessica Richard wrote:
> When you see a hanging Postgres connection (or a job running so long
> and you don't want to continue any more) from
>
> select * from pg_stat_activity
>
> and you want to disconnect it,
>
> how do you do it?
|pg_cancel_backend()
|http://www.postgresql.org/docs/8.1/interactive/functions-ad min.html
--
Tommy Gildseth
DBA, Gruppe for databasedrift
Universitetet i Oslo, USIT
m: +47 45 86 38 50
t: +47 22 85 29 39
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Re: Is there a way to kill a connection from the pg_stat_activitly list?
am 15.10.2007 15:50:15 von jonah.harris
On 10/15/07, Jessica Richard wrote:
> When you see a hanging Postgres connection (or a job running so long and you
> don't want to continue any more) from
>
> select * from pg_stat_activity
>
> and you want to disconnect it,
>
> how do you do it?
See pg_cancel_backend
You should also look at using statement_timeout if this is a regular occurrence.
--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: Is there a way to kill a connection from the pg_stat_activitly list?
am 15.10.2007 19:18:24 von Jessica Richard
--0-1711225073-1192468704=:68410
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Thanks a lot!
"select pg_cancel_backend(procpid) " can end the current query for that u=
ser, but then this connection becomes IDLE, still connected.
Is there a command for me to totally disconnect a user by procpid? Some t=
imes, I need to kick out a particular Postgres user completely.
thanks
Tommy Gildseth wrote: Jessica Richard wrote:
> When you see a hanging Postgres connection (or a job running so long=20
> and you don't want to continue any more) from
>
> select * from pg_stat_activity
>
> and you want to disconnect it,
>
> how do you do it?
|pg_cancel_backend()
|http://www.postgresql.org/docs/8.1/interactive/functions-ad min.html
--=20
Tommy Gildseth
DBA, Gruppe for databasedrift
Universitetet i Oslo, USIT
m: +47 45 86 38 50
t: +47 22 85 29 39
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
=20
---------------------------------
Need a vacation? Get great deals to amazing places on Yahoo! Travel.=20
--0-1711225073-1192468704=:68410
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Thanks a lot!
"select pg_cancel_backend(procpid) " can end the cur=
rent query for that user, but then this connection becomes IDLE, still co=
nnected.
Is there a command for me to totally disconnect a user by=
procpid? Some times, I need to kick out a particular Postgres user compl=
etely.
thanks
Tommy Gildseth <tommy.gildseth@u=
sit.uio.no> wrote:
-left: 2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;">=
Jessica Richard wrote:
> When you see a hanging Postgres connectio=
n (or a job running so long
> and you don't want to continue any m=
ore) from
>
> select * from pg_stat_activity
>
> =
and you want to disconnect it,
>
> how do you do it?
|=
pg_cancel_backend()
|http://www.postgresql.org/docs/8.1/intera=
ctive/functions-admin.html
--
Tommy Gildseth
DBA, Gruppe fo=
r databasedrift
Universitetet i Oslo,
USIT
m: +47 45 86 38 50
t: +47 22 85 29 39
------------=
---------------(end of broadcast)---------------------------
TIP 6: ex=
plain analyze is your friend
Need a vacation?
=3D48256/*http://travel.yahoo.com/;_ylc=3DX3oDMTFhN2hucjlpBF 9TAzk3NDA3NTg=
5BHBvcwM1BHNlYwNncm91cHMEc2xrA2VtYWlsLW5jbQ--">Get great deals=20
to amazing places on Yahoo! Travel.=20
--0-1711225073-1192468704=:68410--
Re: Is there a way to kill a connection from the pg_stat_activitly list?
am 15.10.2007 19:34:27 von Scott Marlowe
On 10/15/07, Jessica Richard wrote:
> Thanks a lot!
>
> "select pg_cancel_backend(procpid) " can end the current query for that
> user, but then this connection becomes IDLE, still connected.
>
> Is there a command for me to totally disconnect a user by procpid? Some
> times, I need to kick out a particular Postgres user completely.
From the command line on the server you can issue a kill to do
that. From within pgsql you'd need to write a function in an
untrusted language to pull it off.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Re: Is there a way to kill a connection from the pg_stat_activitly list?
am 15.10.2007 19:34:50 von jonah.harris
On 10/15/07, Jessica Richard wrote:
> Thanks a lot!
>
> "select pg_cancel_backend(procpid) " can end the current query for that
> user, but then this connection becomes IDLE, still connected.
>
> Is there a command for me to totally disconnect a user by procpid? Some
> times, I need to kick out a particular Postgres user completely.
There used to be a pg_terminate_backend, but it was #ifdef'd out due
to corruption concerns. Basically, all it did was:
kill -TERM pid
I'm not sure whether anyone has completed the research required to
know if anything remains corrupted, but it is used occasionally. Best
to do pg_cancel_backend and then kill -TERM.
--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: Is there a way to kill a connection from the
am 15.10.2007 20:13:23 von Kevin Grittner
>>> On Mon, Oct 15, 2007 at 12:34 PM, in message
<36e682920710151034r2aaef401m5429e460ee0ac209@mail.gmail.com>, "Jonah H.
Harris" wrote:=20
>=20
> There used to be a pg_terminate_backend, but it was #ifdef'd out due
> to corruption concerns. Basically, all it did was:
>=20
> kill -TERM pid
>=20
> I'm not sure whether anyone has completed the research required to
> know if anything remains corrupted, but it is used occasionally. Best
> to do pg_cancel_backend and then kill -TERM.
=20
Where does pg_ctl kill fit in?
=20
Is TERM the normal signal to use there, too?
=20
Should the pg_ctl docs give some guidelines on the signals?
=20
-Kevin
=20
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Re: Is there a way to kill a connection from the pg_stat_activitly list?
am 16.10.2007 13:56:00 von Jessica Richard
--0-122730445-1192535760=:86032
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
If the connection is from the local machine, I can find it with "ps -ef |=
grep procpid", then kill it with Unix command "kill" outside Postgres...
But I have many remote connections coming from different machines...it is=
hard to kill on the OS level outside Postgres on the postgres host...
I am looking for something to kill a Postgres user connection within Post=
gres...
Some thing like, you find the user connection with select * from pg_stat_=
activity...then you pick a procpid and kill right there...
Thanks,
Kevin Grittner wrote: >>> On Mon, Oct 15, 2=
007 at 12:34 PM, in message
<36e682920710151034r2aaef401m5429e460ee0ac209@mail.gmail.com>, "Jonah H.
Harris" wrote:=20
>=20
> There used to be a pg_terminate_backend, but it was #ifdef'd out due
> to corruption concerns. Basically, all it did was:
>=20
> kill -TERM pid
>=20
> I'm not sure whether anyone has completed the research required to
> know if anything remains corrupted, but it is used occasionally. Best
> to do pg_cancel_backend and then kill -TERM.
=20
Where does pg_ctl kill fit in?
=20
Is TERM the normal signal to use there, too?
=20
Should the pg_ctl docs give some guidelines on the signals?
=20
-Kevin
=20
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
=20
---------------------------------
Be a better Globetrotter. Get better travel answers from someone who know=
s.
Yahoo! Answers - Check it out.
--0-122730445-1192535760=:86032
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
If the connection is from the local machine, I can find it with "ps -ef |=
grep procpid", then kill it with Unix command "kill" outside Postgres...=
But I have many remote connections coming from different machines=
....it is hard to kill on the OS level outside Postgres on the postgres ho=
st...
I am looking for something to kill a Postgres user connectio=
n within Postgres...
Some thing like, you find the user connection wit=
h select * from pg_stat_activity...then you pick a procpid and kill right=
there...
Thanks,
Kevin Grittner <Kevin.Grittn=
er@wicourts.gov> wrote:
order-left: 2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5=
px;"> >>> On Mon, Oct 15, 2007 at 12:34 PM, in message
<36=
e682920710151034r2aaef401m5429e460ee0ac209@mail.gmail.com>, "Jonah H.<=
br>Harris" wrote:
>
> There used t=
o be a pg_terminate_backend, but it was
#ifdef'd out due
> to corruption concerns. Basically, all it did =
was:
>
> kill -TERM pid
>
> I'm not sure whethe=
r anyone has completed the research required to
> know if anything =
remains corrupted, but it is used occasionally. Best
> to do pg_ca=
ncel_backend and then kill -TERM.
Where does pg_ctl kill fit in?<=
br>
Is TERM the normal signal to use there, too?
Should the p=
g_ctl docs give some guidelines on the signals?
-Kevin
r>
---------------------------(end of broadcast)------------------=
---------
TIP 5: don't forget to increase your free space map settings=
Be a better Globetrotter.
o.com/evt=3D48254/*http://answers.yahoo.com/dir/_ylc=3DX3oDM TI5MGx2aThyBF=
9TAzIxMTU1MDAzNTIEX3MDMzk2NTQ1MTAzBHNlYwNCQUJwaWxsYXJfTklfMz YwBHNsawNQcm9=
kdWN0X3F1ZXN0aW9uX3BhZ2U-?link=3Dlist&sid=3D396545469">Get better travel =
answers from someone who knows.
Yahoo! Answers - Check it out.
--0-122730445-1192535760=:86032--
Re: Is there a way to kill a connection from the pg_stat_activitly list?
am 16.10.2007 14:00:07 von jonah.harris
On 10/16/07, Jessica Richard wrote:
> If the connection is from the local machine, I can find it with "ps -ef |
> grep procpid", then kill it with Unix command "kill" outside Postgres...
>
> But I have many remote connections coming from different machines...it is
> hard to kill on the OS level outside Postgres on the postgres host...
>
> I am looking for something to kill a Postgres user connection within
> Postgres...
> Some thing like, you find the user connection with select * from
> pg_stat_activity...then you pick a procpid and kill right there...
Write a C stored procedure that takes a pid and calls kill(2), install
it on the server, and call it from SQL.
--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Re: Is there a way to kill a connection from the pg_stat_activitly list?
am 16.10.2007 14:03:30 von Jessica Richard
--0-454119703-1192536210=:90208
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
select pg_cancel_backend(procpid) solved half of my problem...at least it=
terminated the query for that user... but it is still holding a user con=
nection in IDLE state....If I have too many of those, Postgres may run of=
out of user connections....
I already knew how to kill a connection if the connection is from the loc=
al host. But I have many remote connections coming from different machine=
s... hard to kill with unix command "kill"... One time, I was testing to=
kill a particular connection on a testing machine, the entrie Postgres w=
as brought down....
I need to find a safer, cleaner way to disconnect a user from Postgres wh=
en needed.
Thanks a lot,
Scott Marlowe wrote: On 10/15/07, Jessica Richa=
rd wrote:
> Thanks a lot!
>
> "select pg_cancel_backend(procpid) " can end the current query for that
> user, but then this connection becomes IDLE, still connected.
>
> Is there a command for me to totally disconnect a user by procpid? Some
> times, I need to kick out a particular Postgres user completely.
From the command line on the server you can issue a kill=20
to do
that. From within pgsql you'd need to write a function in an
untrusted language to pull it off.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
=20
---------------------------------
Don't let your dream ride pass you by. Make it a reality with Yahoo! A=
utos.=20
--0-454119703-1192536210=:90208
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
select pg_cancel_backend(procpid) solved half of my problem...at least it=
terminated the query for that user... but it is still holding a user con=
nection in IDLE state....If I have too many of those, Postgres may run of=
out of user connections....
I already knew how to kill a connecti=
on if the connection is from the local host. But I have many remote conne=
ctions coming from different machines... hard to kill with unix command "=
kill"... One time, I was testing to kill a particular connection on=
a testing machine, the entrie Postgres was brought down....
I nee=
d to find a safer, cleaner way to disconnect a user from Postgres when ne=
eded.
Thanks a lot,
Scott Marlowe <scott.marlo=
we@gmail.com> wrote:
er-left: 2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;=
"> On 10/15/07, Jessica Richard wrote:
> Thanks=
a lot!
>
> "select
pg_cancel_backend(procpid) " can end the current query for that
> =
user, but then this connection becomes IDLE, still connected.
>
=
> Is there a command for me to totally disconnect a user by procpid? S=
ome
> times, I need to kick out a particular Postgres user complete=
ly.
From the command line on the server you can issue a kill =
to do
that. From within pgsql you'd need to write a function in an
r>untrusted language to pull it off.
---------------------------(e=
nd of broadcast)---------------------------
TIP 2: Don't 'kill -9' the=
postmaster
Don't let your dream ride pass you by.
p://us.rd.yahoo.com/evt=3D51200/*http://autos.yahoo.com/inde x.html;_ylc=3D=
X3oDMTFibjNlcHF0BF9TAzk3MTA3MDc2BHNlYwNtYWlsdGFncwRzbGsDYXV0 b3MtZHJlYW1jY=
XI-"> Make it a reality with Yahoo! Autos.
=20
--0-454119703-1192536210=:90208--
Re: Is there a way to kill a connection from the pg_stat_activitly list?
am 16.10.2007 15:05:48 von jonah.harris
On 10/16/07, Jessica Richard wrote:
> select pg_cancel_backend(procpid) solved half of my problem...at least it
> terminated the query for that user... but it is still holding a user
> connection in IDLE state....If I have too many of those, Postgres may run of
> out of user connections....
>
> I already knew how to kill a connection if the connection is from the local
> host. But I have many remote connections coming from different machines...
> hard to kill with unix command "kill"... One time, I was testing to kill a
> particular connection on a testing machine, the entrie Postgres was brought
> down....
That's why I said to write a C stored procedure to do it and install
it on the server. That way you could call it the same way as
pg_cancel_backend.
> I need to find a safer, cleaner way to disconnect a user from Postgres when
> needed.
At this point in time, there isn't one.
--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Re: Is there a way to kill a connection from the pg_stat_activitly list?
am 17.10.2007 13:16:05 von Moiz Kothari
------=_Part_25344_32202908.1192619765859
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Hi,
Please do not use kill, it sometimes resets all the connection on your db,
you might want to use
pkill -f to kill the connection you want to.
Regards,
Moiz Kothari
On 10/16/07, Jonah H. Harris wrote:
>
> On 10/16/07, Jessica Richard wrote:
> > select pg_cancel_backend(procpid) solved half of my problem...at least
> it
> > terminated the query for that user... but it is still holding a user
> > connection in IDLE state....If I have too many of those, Postgres may
> run of
> > out of user connections....
> >
> > I already knew how to kill a connection if the connection is from the
> local
> > host. But I have many remote connections coming from different
> machines...
> > hard to kill with unix command "kill"... One time, I was testing to
> kill a
> > particular connection on a testing machine, the entrie Postgres was
> brought
> > down....
>
> That's why I said to write a C stored procedure to do it and install
> it on the server. That way you could call it the same way as
> pg_cancel_backend.
>
> > I need to find a safer, cleaner way to disconnect a user from Postgres
> when
> > needed.
>
> At this point in time, there isn't one.
>
> --
> Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
> EnterpriseDB Corporation | fax: 732.331.1301
> 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
> Edison, NJ 08837 | http://www.enterprisedb.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
Hobby Site : http://dailyhealthtips.blogspot.com
------=_Part_25344_32202908.1192619765859
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Hi,
Please do not use kill, it sometimes resets all the connection on your db, you might want to use
pkill -f <string in ps -ef> to kill the connection you want to.
Regards,
Moiz Kothari
On 10/16/07, Jonah H. Harris <> wrote:
On 10/16/07, Jessica Richard <> wrote:
> select pg_cancel_backend(procpid) solved half of my problem...at least it
> terminated the query for that user... but it is still holding a user
> connection in IDLE state....If I have too many of those, Postgres may run of
> out of user connections....
>
> I already knew how to kill a connection if the connection is from the local
> host. But I have many remote connections coming from different machines...
> hard to kill with unix command "kill"... One time, I was testing to kill a
> particular connection on a testing machine, the entrie Postgres was brought
> down....
That's why I said to write a C stored procedure to do it and install
it on the server. That way you could call it the same way as
pg_cancel_backend.
> I need to find a safer, cleaner way to disconnect a user from Postgres when
> needed.
At this point in time, there isn't one.
--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor |
jonah.harris@enterprisedb.com
Edison, NJ 08837 |
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to so that your
message can get through to the mailing list cleanly
--
Hobby Site :
------=_Part_25344_32202908.1192619765859--
Re: Is there a way to kill a connection from the pg_stat_activitly list?
am 17.10.2007 14:00:35 von jonah.harris
On 10/17/07, Moiz Kothari wrote:
> Please do not use kill
More like, please do not take advice from people who don't know what
they're talking about.
> it sometimes resets all the connection on your db,
> you might want to use
Honestly, was this supposed to be a joke? Have you RTFM? Have you
looked at the code to pkill? pkill calls kill and frankly, pkill is
more dangerous if you only want to kill a single session.
Grr, I'm surprised to see such an uninformed statement on this list.
--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org